package com.emmmya.ocs.common.base.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Author: laijieuan
 * Date: 2020-07-07
 * Description: 读取Excel内容
 * T 泛型是接收每行数据的类名
 */
public abstract class ExcelWriter<T> {

	public List<String> CELL_HEADS = new ArrayList<String>(); // 列头



	public List<String> getCELL_HEADS() {
		return CELL_HEADS;
	}

	public void setCELL_HEADS(List<String> cELL_HEADS) {
		CELL_HEADS = cELL_HEADS;
	}



	/**
	 * 生成Excel并写入数据信息,无模板的读取方式
	 *
	 * @param dataList 数据列表
	 * @return 写入数据后的工作簿对象
	 * @throws IOException
	 */
	public Workbook exportData(List<T> dataList,String type) {
		Workbook workbook =null;
		if("xlsx".equals(type)) {
		// 生成xlsx的Excel
		workbook = new XSSFWorkbook();
		}
		else if("xls".equals(type)) {
		// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
		workbook = new HSSFWorkbook();
		}
		Sheet sheet = null;
		//判断是否需要生成第一行的数据头信息
		if(CELL_HEADS!=null && CELL_HEADS.size() >0) {// 生成Sheet表，写入第一行的列头
		 sheet = buildDataSheet(workbook);
		}else {//不要生成
		 sheet = workbook.getSheetAt(0);
		}
		// 构建每行的数据内容
		sheet.autoSizeColumn(1);
		sheet.autoSizeColumn(1, true);
		int rowNum = 1;
		for (Iterator<T> it = dataList.iterator(); it.hasNext();) {
			T data = it.next();
			if (data == null) {
				continue;
			}
			// 输出行数据
			Row row = sheet.createRow(rowNum++);
			convertDataToRow(data, row);
		}
		return workbook;
	}



	/**
	 * 默认生成Excel并写入数据信息,有模板的读取方式
	 *
	 * @param dataList 数据列表
	 * @return 写入数据后的工作簿对象
	 * @throws IOException
	 */
	public Workbook exportDataTem(List<T> dataList,String templateName) throws Exception {
		Workbook workbook =null;
		FileInputStream fis = new FileInputStream(templateName);
		if(templateName.endsWith("xlsx")) {
		// 生成xlsx的Excel
			workbook = new XSSFWorkbook(fis);
		}
		else if(templateName.endsWith("xls")) {
		// 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
			workbook = new HSSFWorkbook(fis);
		}
		Sheet sheet = null;
		//判断是否需要生成第一行的数据头信息
		if(CELL_HEADS!=null && CELL_HEADS.size() >0) {// 生成Sheet表，写入第一行的列头
		 sheet = buildDataSheet(workbook);
		}else {//不要生成
		 sheet = workbook.getSheetAt(0);
		}
		sheet.autoSizeColumn(1);
		// 构建每行的数据内容
		int rowNum = 1;
		for (Iterator<T> it = dataList.iterator(); it.hasNext();) {
			T data = it.next();
			if (data == null) {
				continue;
			}
			// 输出行数据
			Row row = sheet.createRow(rowNum++);
			convertDataToRow(data, row);
		}
		return workbook;
	}

	//自定义构建workbook对象方法
	public abstract Workbook customExportDataTem(List<T> dataList,String templateName) throws Exception;

	/**
	 * 生成sheet表，并写入第一行数据（列头）
	 *
	 * @param workbook 工作簿对象
	 * @return 已经写入列头的Sheet
	 */
	public Sheet buildDataSheet(Workbook workbook) {
		Sheet sheet = workbook.createSheet();
		// 设置列头宽度
		for (int i = 0; i < CELL_HEADS.size(); i++) {
			sheet.setColumnWidth(i, 4000);
		}
		// 设置默认行高
		sheet.setDefaultRowHeight((short) 400);
		// 构建头单元格样式
		//CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());
		// 写入第一行各列的数据
		Row head = sheet.createRow(0);
		for (int i = 0; i < CELL_HEADS.size(); i++) {
			Cell cell = head.createCell(i);
			cell.setCellValue(CELL_HEADS.get(i));
			//cell.setCellStyle(cellStyle);
		}
		return sheet;
	}

	/**
	 * 设置第一行列头的样式
	 *
	 * @param workbook 工作簿对象
	 * @return 单元格样式对象
	 */
	/*
	 * private static CellStyle buildHeadCellStyle(Workbook workbook) { CellStyle
	 * style = workbook.createCellStyle(); // 对齐方式设置
	 * style.setAlignment(HorizontalAlignment.CENTER); // 边框颜色和宽度设置
	 * style.setBorderBottom(BorderStyle.THIN);
	 * style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
	 * style.setBorderLeft(BorderStyle.THIN);
	 * style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
	 * style.setBorderRight(BorderStyle.THIN);
	 * style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
	 * style.setBorderTop(BorderStyle.THIN);
	 * style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框 // 设置背景颜色
	 * style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	 * style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 粗体字设置 Font font =
	 * workbook.createFont(); font.setBold(true); style.setFont(font); return style;
	 * }
	 */

	/**
	 * 加载Map，根据key的单元格位置来
	 * 导出workbook对象
	 */
	public abstract void customInsertToCell(Map<String,Object> dataMap,Workbook workbook);

	public abstract void customInsertToCell(Map<String,Object> dataMap,Sheet sheet);


	/**
     *
     * <p class="detail">
     * 描述：临时单元格数据
     * </p>
     * @ClassName: Cell
     * @version V1.0
     * @date 2015年9月26日
     * @author <a href="mailto:1435290472@qq.com">zq</a>
     */
    public static class TempCell{
        private int row;
        private int column;
        private CellStyle cellStyle;
        private Object data;
        //用于列表合并，表示几列合并
        private int columnSize = -1;

        public int getColumn() {
            return column;
        }
        public void setColumn(int column) {
            this.column = column;
        }
        public int getRow() {
            return row;
        }
        public void setRow(int row) {
            this.row = row;
        }
        public CellStyle getCellStyle() {
            return cellStyle;
        }
        public void setCellStyle(CellStyle cellStyle) {
            this.cellStyle = cellStyle;
        }
        public Object getData() {
            return data;
        }
        public void setData(Object data) {
            this.data = data;
        }
        public int getColumnSize() {
            return columnSize;
        }
        public void setColumnSize(int columnSize) {
            this.columnSize = columnSize;
        }

    }
    //给单元格设置值
    public void setCell(TempCell tempCell,Sheet sheet) {
        if(tempCell.getColumnSize()>-1){
            CellRangeAddress rangeAddress = mergeRegion(sheet, tempCell.getRow(), tempCell.getRow(), tempCell.getColumn(), tempCell.getColumn()+tempCell.getColumnSize());
            setRegionStyle(tempCell.getCellStyle(), rangeAddress, sheet);
        }

        Row rowIn = sheet.getRow(tempCell.getRow());
        if(rowIn == null) {
            rowIn = sheet.createRow(tempCell.getRow());
        }
        Cell cellIn = rowIn.getCell(tempCell.getColumn());
        if(cellIn == null) {
            cellIn = rowIn.createCell(tempCell.getColumn());
        }
        //根据data类型给cell赋值
        if(tempCell.getData() instanceof String){
            cellIn.setCellValue((String)tempCell.getData());
        }else if(tempCell.getData() instanceof Integer){
            cellIn.setCellValue(Integer.parseInt(tempCell.getData().toString()));
        }
        else if(tempCell.getData() instanceof Double) {
        	cellIn.setCellValue(Double.parseDouble(tempCell.getData().toString()));
        }
        else{
            cellIn.setCellValue((String)tempCell.getData());
        }
        //样式
        if(tempCell.getCellStyle()!=null && tempCell.getColumnSize()==-1){
            cellIn.setCellStyle(tempCell.getCellStyle());
        }

    }

    private CellRangeAddress mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress rang = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(rang);
        return rang;
    }
    /**
     *
     * 功能：设置合并单元格样式
     * @param cs
     * @param region
     * @param sheet
     */
    private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet){
        for(int i=region.getFirstRow();i<=region.getLastRow();i++){
            Row row=sheet.getRow(i);
            if(row==null) row=sheet.createRow(i);
            for(int j=region.getFirstColumn();j<=region.getLastColumn();j++){
                Cell cell=row.getCell(j);
                if(cell==null){
                    cell=row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }

	/**
	 * 获取单元格
	 */
	public TempCell getCell(String point,Object data,Sheet sheet){
        TempCell tempCell = new TempCell();
        //得到列 字母
        String lineStr = "";
        String reg = "[A-Z]+";
        Pattern p = Pattern.compile(reg);
        Matcher m = p.matcher(point);
        while(m.find()){
            lineStr = m.group();
        }
        //将列字母转成列号  根据ascii转换 目前只能转化单字母
        /*char[] ch = lineStr.toCharArray();
        int column = 0;
        for(int i=0;i<ch.length;i++){
            char c = ch[i];
            int post = ch.length-i-1;
            int r = (int) Math.pow(10, post);
            column = column + r*((int)c-65);
        }*/
        int colIndex = excelColStrToNum(lineStr, lineStr.length());
        tempCell.setColumn(colIndex-1);

        //得到行号
        reg = "[0-9]+";
        p = Pattern.compile(reg);
        m = p.matcher(point);
        while(m.find()){
            tempCell.setRow((Integer.parseInt(m.group())-1));
        }

        //获取模板指定单元格样式，设置到tempCell （写列表数据的时候用）
        Row rowIn = sheet.getRow(tempCell.getRow());
        if(rowIn == null) {
            rowIn = sheet.createRow(tempCell.getRow());
        }
        Cell cellIn = rowIn.getCell(tempCell.getColumn());
        if(cellIn == null) {
            cellIn = rowIn.createCell(tempCell.getColumn());
        }
        tempCell.setCellStyle(cellIn.getCellStyle());
        tempCell.setData(data);
        return tempCell;
    }

	//字符串转number
	public static int excelColStrToNum(String colStr, int length) {
	      int num = 0;
	      int result = 0;
	      for(int i = 0; i < length; i++) {
	        char ch = colStr.charAt(length - i - 1);
	        num = (int)(ch - 'A' + 1) ;
	        num *= Math.pow(26, i);
	        result += num;
	      }
	      return result;
	    }

	/**
	 * 将数据转换成行
	 *
	 * @param data 源数据
	 * @param row  行对象
	 * @return
	 */
	public abstract void convertDataToRow(T data, Row row);// {
//		int cellNum = 0;
//		Cell cell;
//		// 姓名
//		cell = row.createCell(cellNum++);
//		cell.setCellValue(null == data.getName() ? "" : data.getName());
//		// 年龄
//		cell = row.createCell(cellNum++);
//		if (null != data.getAge()) {
//			cell.setCellValue(data.getAge());
//		} else {
//			cell.setCellValue("");
//		}
//		// 所在城市
//		cell = row.createCell(cellNum++);
//		cell.setCellValue(null == data.getLocation() ? "" : data.getLocation());
//		// 职业
//		cell = row.createCell(cellNum++);
//		cell.setCellValue(null == data.getJob() ? "" : data.getJob());
//	}

	/**
	 * 将数据写入某个单元格
	 * @return
	 */
	public boolean setCellValue(Workbook wb,Integer sheetIndex ,Integer rowNumber ,Integer cellNumber,String cellValue){
		Sheet sheetAt = wb.getSheetAt(sheetIndex);
		if(sheetAt ==null) {
			sheetAt = wb.createSheet("未命名的sheet");
		}
		return setCellValue(sheetAt,rowNumber ,cellNumber,cellValue);
	}

	public boolean setCellValue(Sheet sheet,Integer rowNumber ,Integer cellNumber,String cellValue){
		Row row = sheet.getRow(rowNumber);
		if(row ==null) {
			row = sheet.createRow(rowNumber);
		}
		return setCellValue(row,cellNumber, cellValue);
	}

	public boolean setCellValue(Row row,Integer cellNumber,String cellValue){
		try {
			Cell cell = row.getCell(cellNumber);
			if(cell ==null) {
				cell = row.createCell(cellNumber);
			}
			//cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(cellValue);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean setCellValue(Row row,CellStyle style,Integer cellNumber,String cellValue){
		try {
			Cell cell = row.getCell(cellNumber);
			if(cell ==null) {
				cell = row.createCell(cellNumber);
			}
			cell.setCellStyle(style);
//			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(cellValue);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

}